package com.fjh.springboot_01.mapper;

import com.fjh.springboot_01.pojo.tutor.TutoringBooking;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface TutoringBookingMapper {
    @Insert("INSERT INTO tutoring_booking (tutoring_id, student_id, teacher_id, start_time, " +
            "end_time, location, requirements, status, create_time, update_time) " +
            "VALUES (#{tutoringId}, #{studentId}, #{teacherId}, #{startTime}, " +
            "#{endTime}, #{location}, #{requirements}, #{status}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(TutoringBooking booking);

    @Select("SELECT tb.*, " +
            "tu.title as tutoring_title, " +
            "t.username as teacher_name, t.user_pic as teacher_avatar, " +
            "s.username as student_name, s.user_pic as student_avatar " +
            "FROM tutoring_booking tb " +
            "LEFT JOIN tutoring tu ON tb.tutoring_id = tu.id " +
            "LEFT JOIN user t ON tb.teacher_id = t.id " +
            "LEFT JOIN user s ON tb.student_id = s.id " +
            "WHERE tb.teacher_id = #{teacherId} AND (tb.teacher_deleted IS NULL OR tb.teacher_deleted = 0) " +
            "ORDER BY tb.create_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle"),
        @Result(column = "teacher_avatar", property = "teacherAvatar"),
        @Result(column = "student_avatar", property = "studentAvatar")
    })
    List<TutoringBooking> findByTeacherId(Integer teacherId);

    @Select("SELECT tb.*, " +
            "tu.title as tutoring_title, " +
            "t.username as teacher_name, t.user_pic as teacher_avatar, " +
            "s.username as student_name, s.user_pic as student_avatar " +
            "FROM tutoring_booking tb " +
            "LEFT JOIN tutoring tu ON tb.tutoring_id = tu.id " +
            "LEFT JOIN user t ON tb.teacher_id = t.id " +
            "LEFT JOIN user s ON tb.student_id = s.id " +
            "WHERE tb.student_id = #{studentId} AND (tb.student_deleted IS NULL OR tb.student_deleted = 0) " +
            "ORDER BY tb.create_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle"),
        @Result(column = "teacher_avatar", property = "teacherAvatar"),
        @Result(column = "student_avatar", property = "studentAvatar")
    })
    List<TutoringBooking> findByStudentId(Integer studentId);

    @Update("UPDATE tutoring_booking SET status = #{status}, update_time = NOW() " +
            "WHERE id = #{id}")
    void updateStatus(@Param("id") Integer id, @Param("status") Integer status);

    @Select("SELECT b.*, " +
            "s.username as student_name, s.user_pic as student_avatar, " +
            "t.username as teacher_name, t.user_pic as teacher_avatar, " +
            "tu.title as tutoring_title " +
            "FROM tutoring_booking b " +
            "LEFT JOIN user s ON b.student_id = s.id " +
            "LEFT JOIN user t ON b.teacher_id = t.id " +
            "LEFT JOIN tutoring tu ON b.tutoring_id = tu.id " +
            "WHERE b.id = #{id}")
    @Results({
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "tutoring_title", property = "tutoringTitle"),
        @Result(column = "student_avatar", property = "studentAvatar"),
        @Result(column = "teacher_avatar", property = "teacherAvatar")
    })
    TutoringBooking findById(Integer id);
    
    /**
     * 检查学生和教师之间是否存在指定状态的预约关系
     * @param studentId 学生ID
     * @param teacherId 教师ID
     * @param status 预约状态（0-待确认，1-已确认，2-已拒绝）
     * @return 符合条件的预约数量
     */
    @Select("SELECT COUNT(*) FROM tutoring_booking " +
            "WHERE student_id = #{studentId} AND teacher_id = #{teacherId} AND status = #{status}")
    int checkStudentTeacherBooking(@Param("studentId") Integer studentId, 
                                  @Param("teacherId") Integer teacherId, 
                                  @Param("status") Integer status);

    /**
     * 获取学生已确认预约的教师ID列表
     * @param studentId 学生ID
     * @return 教师ID列表
     */
    @Select("SELECT DISTINCT teacher_id FROM tutoring_booking " +
            "WHERE student_id = #{studentId} AND status = 1")
    List<Integer> findConfirmedTeacherIdsByStudentId(Integer studentId);

    /**
     * 获取已完成家教的数量
     * @return 已完成家教的数量
     */
    @Select("SELECT COUNT(*) FROM tutoring_booking WHERE status = 3")
    int getCompletedBookingCount();

    @Update("UPDATE tutoring_booking SET teacher_deleted = 1 WHERE id = #{id}")
    void markTeacherDeleted(Integer id);

    @Update("UPDATE tutoring_booking SET student_deleted = 1 WHERE id = #{id}")
    void markStudentDeleted(Integer id);

    @Delete("DELETE FROM tutoring_booking WHERE id = #{id} AND teacher_deleted = 1 AND student_deleted = 1")
    void deleteById(Integer id);

    /**
     * 根据学生ID和教师ID查询已确认的预约
     * @param studentId 学生ID
     * @param teacherId 教师ID
     * @return 预约信息
     */
    @Select("SELECT * FROM tutoring_booking WHERE student_id = #{studentId} AND teacher_id = #{teacherId} AND status = 1 ORDER BY create_time DESC LIMIT 1")
    TutoringBooking findBookingByStudentAndTeacherId(@Param("studentId") Integer studentId, @Param("teacherId") Integer teacherId);
} 